Microsoft SQL Server supports the following types of backups:

The entire database can be recreated from a database backup in one step by restoring the database. The restore process overwrites the existing database or creates the database if it does not exist. The restored database will match the state of the database at the time the backup completed, minus any uncommitted transactions. Uncommitted transactions are rolled back when the database is recovered.

Based on the resource requirements, the DBA can also choose the recovery model for the database. The recovery model balances logging overhead against the criticality of fully recovering the data.

The recovery models supported by Microsoft SQL Server are:

Recovering to a point-in-time (for example, a time before unwanted data was entered) requires either full or bulk-logged recovery models.

A full database backup creates a duplicate of the data that is in the database. This is a single operation, usually scheduled at regular intervals. Full database backups are self-contained. Full backups provide a snapshot of the database. Most of the recovery options require a full backup to be present.

We strongly recommend the use of full backups.

A differential database backup records only the data that has changed since the last database backup. Frequent differential backups are recommended to reduce backup times. Making frequent backups decreases the risk of losing data.

Differential backups restore the data that they contain to the database. Differential backups cannot be used to recover the database to a point in time.

The availability of a differential backup minimizes the time it takes to roll forward transaction log backups when restoring a database.

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time or to the point of failure.

When restoring a transaction log backup, Microsoft SQL Server rolls forward all the changes recorded in the transaction log. When Microsoft SQL Server reaches the end of the transaction log, it has recreated the exact state of the database at the time of the backup operation.

If the database is recovered, Microsoft SQL Server then rolls back all transactions that were incomplete when the backup operation started.

Transaction log backups generally use fewer resources than database backups. As a result, you can create them more frequently than database backups. Frequent backups decrease the risk of losing data. For high volume Online Transaction Processing (OLTP) environments, it is desirable to create transaction log backups more frequently. Transaction log backups can only be used with Full and bulk-logged recovery models.

The transaction log cannot be backed up during a full database backup or a differential database backup. However, the transaction log can be backed up while a file backup is running.

Never backup a transaction log before a database backup is created because the transaction log contains the changes made to the database after the last backup was created.

Never truncate the transaction log manually because it breaks the backup chain. If a transaction log has been truncated, take a full database backup to start a new backup chain.

A file or file group backup consists of the backing up of individual data files (or the files in the file group). A file-based recovery model increases the speed of recovery by allowing you to restore only the damaged files without restoring the rest of the database. For example, suppose a database is comprised of several files located physically on different disks and one disk fails. Only the file on the failed disk needs to be restored and rebuilt using the transaction log backup.

File backup and restore operations must be used in conjunction with transaction log backups. For this reason, file backups can only be used with the full recovery and bulk-logged recovery models.

We recommend that you:

• Use the full recovery model.
• Perform a full database backup once every day. For full database sizes greater than 3 GB, it is okay to perform full backups on alternate days. If you perform full backups on alternate days, we strongly recommend that you create daily differential backups.
• Create daily transaction log backups after the completion of the full or differential backup. In addition to this, schedule a transaction log backup every 4 hours. Never truncate a transaction log manually.
• In case of a disaster, create a backup of the currently active transaction log. If active transaction log backup is not available (for example, because a media failure for the drive hosting the transaction logs and drive is not being mirrored), the database cannot be recovered past the last available transaction log backup. This would hamper a point-in-time recovery beyond the last available transaction log backup.

Typically an Oracle DBA uses one or more of the following options to back up an Oracle database.

Oracle exports are logical database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back up the physical data files. One of the advantages of exports is that you can selectively re-import tables. However, you cannot roll forward from a restored export file. To completely restore a database from an export file, you almost need to recreate the entire database. Logical backups takes a snapshot of the database schema as it was at a particular time.

A backup performed when the database is shut down is known as an offline or cold backup. You must copy the data files, control file and online redo log files using an OS copy utility. This is a considered a complete backup of the database. Any changes made after this backup are unrecoverable if the database is running in NOARCHIVELOG mode. All transactions are recorded in online redo log files whether the database is archiving or not. When redo logs are archived (ARCHIVELOG mode), Oracle allows you to apply these transactions after restoring files that were damaged (assuming that an active redo log file was not among the files damaged).

Whenever the schema of the database is changed, such as when you add a new data file, rename a file, or create or drop a tablespace is created, you must shut down the database and copy at least the control file and the newly added data file. A complete backup of the database is preferred.

Before performing a cold backup, it is essential to get a list of all the Oracle files that need to be backed up. Running the following queries will provide a list of all the files.

select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Shut down the database from SQL*Plus or Server Manager. Back up all the files to secondary storage (for example, tapes). Ensure that you back up all data files, all control files, and all log files. When completed, restart your database.

A backup performed when the database instance is running is known as online or hot backup. Online backups are very important at customer sites where a database instance must operate 24-hours per day and offline backups are not feasible. During the duration of an online backup, the database remains available for both reading and updating. For this kind of backup, the database must be in ARCHIVELOG mode. Only data files and current control file need to be backed up. Unlike offline backups, the unit of a online backup is a tablespace, and any or all tablespaces can be backed up whenever needed. Different data files can be backed up at different times.

To perform an online backup, you switch the tablespace into “backup mode” before copying the files as shown in the following example.

ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;

It is better to backup individual tablespaces than to put all tablespaces in backup mode at the same time. Backing them up separately incurs less overhead. After completing the tablespace backups, it is important to back up the control files as shown in the following example.

ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/<directory name>/control.dbf';

The frequency of online backups is inversely proportional to the time taken for recovery in case of a media failure. The older your backup, the more redo log files need to be applied, and the recovery times increases. Backup strategies should be tested before being used to protect a production database.

We strongly recommend that you run online backups at times when the database is least accessed, during non-peak hours. Oracle writes complete database blocks instead of the normal deltas to redo log files while in backup mode. This leads to excessive database archiving and even database freezes.

Recovery Manager (RMAN) is an Oracle tool that lets the DBA back up and recover Oracle databases. RMAN lets you perform full backups (with the database online or offline), incremental backups on the block level, and backups of online redo logs and control files.

The SYSDBA privilege is required to run RMAN on a database. The other benefits of RMAN backups are that you can:

• Keep track of all backup and recovery operations performed against the database.
• Manage centralized backup and recovery procedures for the enterprise.
• Identify corrupt blocks.
• Back up only those blocks that actually contain data. This can lead to significant savings in backup space requirements.
• Have support for incremental backups. Incremental backups back up only those blocks that have changed since a previous backup. This helps with the disk space usage and reduces the backup times significantly. Oracle 10g has introduced a new feature called “block change training”. This feature provides significant improvement for incremental backups. Contact your DBA about how to implement this feature.

The following examples of RMAN backup and restore are extremely simplistic and are included on to illustrate basic concepts. By default, Oracle uses the database control files to store information about backups. Normally, you will prefer to set up an RMAN catalog database to store RMAN metadata. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.

rman target sys/*** nocatalog
 run { 
  allocate channel t1 type disk; 
  backup format '/app/oracle/db_backup/%d_t%t_s%s_p%p' (database);
  release channel t1;
 }
Example RMAN restore:
 rman target sys/*** nocatalog
 run {
  allocate channel t1 type disk;
  restore tablespace users;
  recover tablespace users;
  release channel t1;
 }  

Oracle introduced the export/import data pump in the 10g release. The import pump is twenty times faster than the conventional import utility. Export/Import data pump utilities are logical database backups (not physical) as they extract data and logical definitions from the database into a file. Export/Import data pump utilities do not fit into 24/7 model because they do not offer roll-forward capabilities. Export data pump provides a snapshot of the database schema as it was at a particular time.

We strongly recommend the use of RMAN backups if your enterprise wants to run a StarTeam Server instance in a 24/7 environment. RMAN has evolved over the last few years and Oracle Corporation continues to add features that make disaster recovery easier, more reliable, and faster.

PostgreSQL database provides easy backup and restore utilities. There are two different types of backups you can use:

Use the following commands to manage your PostgreSQL databases.

 pg_dump -U postgres -h <hostname> -p 5432 -F c -f Atlas.backup <Database Name>

createdb -U postgres -h <New Hostname> -p 5432 <New Db Name>

 pg_restore -U postgres -h <New Hostname> -p 5432 -d <New Db Name> Atlas.backup